from usagi.paths import *
import gzip
import sys
try:
    from sqlite3 import dbapi2 as sql # python 2.5
except:
    try:
        from pysqlite2 import dbapi2 as sql
    except:
        print 'This program requires pysqlite2\n',\
        'http://initd.org/tracker/pysqlite/'
    sys.exit(1)

class sql_cur:
    def __init__(self):
        self.con    =sql.connect(SQL_PATH)
        self.cur    =self.con.cursor()
        self.fetch_learned()

    def add_kanji(self,k):
        print "Added %s to list." %k
        self.cur.execute('UPDATE kanjis SET mastered=1 WHERE kanji=(?)', (unicode(k),))
        self.con.commit()

    def mark_word_mastered(self,w,m=1):
        print "Marked %s as %smastered." % (w, m and " " or "un")
        self.cur.execute("UPDATE tango SET mastered=(?), date='now' WHERE word=(?)", (m,unicode(w),))
        self.con.commit()
        
    def set_word_comment(self,w,c):
        print "Commented \"%s\" on %s." % (c,w)
        self.cur.execute("UPDATE tango SET comment=(?) WHERE word=(?)", (unicode(c),unicode(w),))
        self.con.commit()

    def add_tango(self,tango_list):
        for word in tango_list:
            self.cur.execute('INSERT INTO tango VALUES ((?),0,0,"")', (unicode(word),))

    def _get_kanjis(self, m=0):
        self.cur.execute('SELECT kanji FROM kanjis WHERE mastered=(?)', (m,))

    def _get_tango(self, m=0):
        self.cur.execute("SELECT word, comment FROM tango WHERE mastered=(?) and date<datetime('now','+2 hours')", (m,))


    def fetch_word(self, refetch=False):
        one = self.cur.fetchone()
        if not one or refetch:
            self._get_tango()
            one = self.cur.fetchone()
        return one or None

    def fetch_kanji(self, refetch=False):
        one = self.cur.fetchone()
        if not one or refetch:
            self._get_kanjis()
            one = self.cur.fetchone()
        return one[0]

    def fetch_learned(self):
        self._get_kanjis(1)
        tpld = self.cur.fetchall()
        return map(lambda i:i[0], tpld)

    def __destroy__(self):
        self.cur.close()

    def close(self):
        self.__destroy__()



def create_database():
    f = gzip.open(JOUYOU_PATH, 'r')
    jouyouexkan= unicode(f.read(),'UTF-8').strip()
    CREATE_HOME()
    con = sql.connect(SQL_PATH)
    cur = con.cursor()
    cur.execute('CREATE TABLE kanjis (kanji nchar(3), mastered BOOLEAN)')
    cur.execute('CREATE TABLE tango  (word STRING CONSTRAINT primindex PRIMARY KEY, mastered BOOLEAN, date SMALLDATETIME, comment STRING DEFAULT "")')
    for k in jouyouexkan:
        cur.execute('INSERT INTO kanjis VALUES ((?),0)', (k,))
    con.commit()
    cur.close()

